package com.hospital.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.hospital.model.JdbcConfig;

//封装数据库常用操作类

public class DbUtil {

	//取得Connection
	public static Connection getConnection() {
		Connection conn = null;
		JdbcConfig jdbcConfig = XmlConfigReader.getInstance().getJdbcConfig();
		try {
			Class.forName(jdbcConfig.getDrivername());
			//此处如此设置可以保证系统在不同系统上不会出现乱码
			//使用的是XML单利类解析的方法进行数据库的解析
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hospital?useUnicode=true&characterEncoding=UTF-8",
					jdbcConfig.getUsername(), jdbcConfig.getPassword());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//返回所取得的连接
		return conn;
	}

	// 关闭Connection
	public static void close(Connection conn) {
		//关闭之前首先要进行空判断
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//关闭PreparedStatement
	public static void close(PreparedStatement pstmt) {
		//关闭之前首先要进行空的判断
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//关闭ResultSet
	public static void close(ResultSet rs) {
		//关闭之前首先要进行空的判断
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//开始事务
	public static void beginTransaction(Connection conn) {
		try {
			//关闭之前首先要进行空的判断
			if (conn != null) {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false); // 手动提交
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//提交事务
	public static void commitTransaction(Connection conn) {
		try {
			if (conn != null) {
				if (!conn.getAutoCommit()) {
					conn.commit();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//回滚事务
	public static void rollbackTransaction(Connection conn) {
		try {
			if (conn != null) {
				if (!conn.getAutoCommit()) {
					conn.rollback();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//重置连接
	public static void resetConnection(Connection conn) {
		try {
			if (conn != null) {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false);
				} else {
					conn.setAutoCommit(true);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//删除（主要是根据表明和id号码作为主键进行删除）
	public static boolean del(String tablename, String[] id) {
		boolean flag = false;
		StringBuilder sbStr = new StringBuilder();
		for (int i = 0; i < id.length; i++) {
			sbStr.append(id[i]).append(",");
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from " + tablename + " where id in (" + sbStr.substring(0, sbStr.length() - 1) + ")";
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			int t = pstmt.executeUpdate();
			if (t >= 1) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}
		return flag;
	}

	//删除部门
	public static boolean deldeptId(String tablename, String[] id) {
		boolean flag = false;
		StringBuilder sbStr = new StringBuilder();
		for (int i = 0; i < id.length; i++) {
			sbStr.append(id[i]).append(",");
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from " + tablename + " where deptId in (" + sbStr.substring(0, sbStr.length() - 1) + ")";
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			int t = pstmt.executeUpdate();
			if (t >= 1) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}
		return flag;
	}
	//根据病人的id号进行删除
	public static boolean delBypatientId(String tablename, String[] patientId) {
		boolean flag = false;
		StringBuilder sbStr = new StringBuilder();
		for (int i = 0; i < patientId.length; i++) {
			sbStr.append(patientId[i]).append(",");
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from " + tablename + " where patientId in (" + sbStr.substring(0, sbStr.length() - 1) + ")";
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			int t = pstmt.executeUpdate();
			if (t >= 1) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}
		return flag;
	}

	//添加或者修改
	public static boolean add_modify(String sql) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			int t = pstmt.executeUpdate();
			if (t >= 1) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(conn);
		}
		return flag;
	}

	//获取所以记录数
	public static int getCount(String tablename) {
		int count = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from " + tablename;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return count;
	}
	//获取远程挂号病患总数
	public static int getCountByPatientOrder(String tablename,String patientname) {
		int count = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String name = patientname;
		String sql = "select count(*) from "+tablename+"where name=?";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return count;
	}
	//获取需要住院病患总数
	public static int getCountPatient(String tablename) {
		int count = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from " + tablename + "where Inhospitable = 'yes'";
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return count;
	}
	//获取所以记录数
	public static int getCountBySql(String sql) {
		int count = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return count;
	}

	//获取记录总和
	public static double getTotal(String sql) {
		double count = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				count = rs.getDouble(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return count;
	}

	public static String getString(String sql) {
		String str = "";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				str = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return str;
	}

	public static int getInt(String sql) {
		int i = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				i = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close(stmt);
			close(conn);
		}
		return i;
	}

	//通过sql语句执行
	public static List<Map<String, Object>> find(String sql) {
		Connection conn = getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	//根据sql语句进行名字的查询
	public static String[] findName(String sql) {
		Connection conn = getConnection();
		String[] name = null;
		int i = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData();
			while (rs.next()) {
				name[i++] = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}
	///销售判断 判断库存是否够
	public int getIf(String ypmc){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql="select ypsl from t_stroage where ypmc='"+ypmc+"'";
		DbUtil dbutil = new DbUtil();
		conn = dbutil.getConnection();
		try{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			if(rs.next())
				return rs.getInt(1);
			else
				return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public int getSale(String ypmc){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		DbUtil dbutil = new DbUtil();
		conn = dbutil.getConnection();
		String sql = "select sum(ypsl) from t_sale where ypmc='"+ypmc+"'"; 
		try{
			stmt = conn.prepareStatement(sql);
			rs=stmt.executeQuery(sql);
			if(rs.next())
				return rs.getInt(1);
			else
				return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}